package dataBase;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import auxiliar.GestorTurno;
import auxiliar.Turno;

import servicios.Servicio;
import clientes.Cliente;

public class JDBCServicioDAO {
	
	private Connection conn;
	
	
	public JDBCServicioDAO(){
		conn = ConnectionManager.getInstance().checkOut();
	}
	
	public void finalice(){
		ConnectionManager.getInstance().checkIn(conn);
	}
	//gestores auxiliares
	GestorTurno gt = new GestorTurno();
	//crea un servicio nuevo
	public void insertServicio(Servicio s) {
		Connection conn = ConnectionManager.getInstance().checkOut();

		String sql = "INSERT INTO servicios (nombre_servicio, fecha_inicio, fecha_fin, CIF_DNI_cliente, numDias) VALUES (?, ?, ?, ?, ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            
            stmt.setString(1, s.getNombreServicio());
            stmt.setDate(2, s.getFechaIni());
            stmt.setDate(3, s.getFecha_fin());
            stmt.setString(4, s.getCIF_cliente());
            stmt.setInt(5, s.getNumDias());

            stmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
	}
	
	//muestra un servicio pasandole un cliente y un nombre de servicio
	public Servicio selectServicio(String nombre, Cliente c){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        Servicio s = null;
       
        String sql = "SELECT * FROM servicios WHERE (nombre_servicio = ? AND CIF_DNI_cliente = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, nombre);
            stmt.setString(2, c.getDNI_CIF_cliente());
            result = stmt.executeQuery();

            if(result.next()){
            	s = new Servicio();
            	//e.setNombre(result.getString("nombre"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setFechaIni(result.getDate("fecha_inicio"));
            	s.setFecha_fin(result.getDate("fecha_fin"));
            	s.setCIF_cliente(result.getString("CIF_DNI_cliente"));
            	s.setNumDias(result.getInt("numDias"));
            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return s;
	}
	
	//muestra la cantidad de vigilantes de un tipo que requiere un servicio
	public int selectNumTipoServicio(Servicio s, String tipoEmp){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        int res=0;
       
        String sql = "SELECT * FROM servicios_tipostrabajadores WHERE (descripcion = ? AND nombre_servicio = ? AND dni_cliente = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, tipoEmp);
            stmt.setString(2, s.getNombreServicio());
            stmt.setString(3, s.getCIF_cliente());
            result = stmt.executeQuery();

            if(result.next()){
            	res= result.getInt("cantidad");
            	
            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return res;
	}
	//muestra un servicio pasandole un dni de un cliente
	/*public Servicio selectServicioPorDNI(String dni){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        Servicio s = null;
       
        String sql = "SELECT * FROM servicios WHERE (CIF_DNI_cliente = ?) ";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, dni);
            result = stmt.executeQuery();

            if(result.next()){
            	s = new Servicio();
            	//e.setNombre(result.getString("nombre"));
            	s.setNombreServicio(result.getString("nombre_servicio"));
            	s.setFechaIni(result.getDate("fecha_inicio"));
            	s.setFecha_fin(result.getDate("fecha_fin"));
            	s.setCIF_cliente(result.getString("CIF_DNI_cliente"));
            	s.setNumDias(result.getInt("numDias"));
            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return s;
	}*/
	
	//muestra una lista de servicios pasandole el dni del cliente
	public List<Servicio> selectServicioCliente(Cliente c){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Servicio> lserv = new LinkedList<Servicio>();
        
        String sql = "SELECT * FROM servicios WHERE(CIF_DNI_cliente = ?) ORDER BY nombre_servicio";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, c.getDNI_CIF_cliente());
            result = stmt.executeQuery();

            while (result.next()) {
                Servicio s = new Servicio();
                
                s.setNombreServicio(result.getString("nombre_servicio"));
                s.setFechaIni(result.getDate("fecha_inicio"));   
                s.setFecha_fin(result.getDate("fecha_fin"));
                s.setCIF_cliente(result.getString("CIF_DNI_cliente"));
                s.setNumDias(result.getInt("numDias"));
                lserv.add(s);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return lserv;
	}
	
	//muestra una lista de turnos pasandole el servicio y el tipo de trabajador
	public List<Turno> selectTurnosServicio(Servicio s,String tipoTrab){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Turno> lt = new LinkedList<Turno>();
        
        String sql = "SELECT * FROM servicios_turnos WHERE(nombre_servicio = ? AND dni_cliente=? AND descripcion=?)";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombreServicio());
            stmt.setString(2, s.getCIF_cliente());
            stmt.setString(3, tipoTrab);
            result = stmt.executeQuery();

            while (result.next()) {
                Turno t = new Turno();
                Time hora_i= result.getTime("hora_inicio");
                Time hora_f= result.getTime("hora_fin");
                t=gt.mostrarTurno(hora_i, hora_f);
                lt.add(t);
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return lt;
	}
	//muestra una lista de turnos pasandole el servicio, el tipo de trabajador y el tipo de turno
	public List<Turno> selectTipoTurnosServicio(Servicio s,String tipoTrab,String tipoTurno){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        List<Turno> lt = new LinkedList<Turno>();
        
        String sql = "SELECT * FROM servicios_turnos WHERE(nombre_servicio = ? AND dni_cliente=? AND descripcion=?)";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombreServicio());
            stmt.setString(2, s.getCIF_cliente());
            stmt.setString(3, tipoTrab);
            result = stmt.executeQuery();

            while (result.next()) {
                Turno t = new Turno();
                Time hora_i= result.getTime("hora_inicio");
                Time hora_f= result.getTime("hora_fin");
                t=gt.mostrarTurno(hora_i, hora_f);
                if(t.getTurno().equals(tipoTurno)){
                lt.add(t);
                }
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return lt;
	}
	//insertar fila en tabla servicios_tipostrabajadores
	public void insertServicioTipoTrabajador(Map<String,Integer> tt, Servicio s) {
		Iterator<Entry<String, Integer>> it = tt.entrySet().iterator();
        while (it.hasNext()) {
        	Map.Entry<String, Integer> tipoT = (Map.Entry<String, Integer>)it.next();
        
			Connection conn = ConnectionManager.getInstance().checkOut();
	
			String sql = "INSERT INTO servicios_tipostrabajadores (cantidad, descripcion, nombre_servicio, dni_cliente) VALUES (?, ?, ?, ?) ";
	        PreparedStatement stmt = null;
	
	        try {
	            stmt = conn.prepareStatement(sql);
	            
		    	
		        stmt.setInt(1,tipoT.getValue());
		        stmt.setString(2,tipoT.getKey());
		        stmt.setString(3, s.getNombreServicio());
		        stmt.setString(4, s.getCIF_cliente());
	
	            stmt.executeUpdate();
	
	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	            	conn.close();
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
        }
	}
	
	//dado un servicio y un cliente devuelve un Map de tipo de trabajador y cantidad
	public Map<String,Integer> selectMapTrabajadores(Servicio s, Cliente c){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        Map<String, Integer> Mserv = new HashMap<String, Integer>();
        
        String sql = "SELECT * FROM servicios_tipostrabajadores WHERE(nombre_servicio = ? AND dni_cliente = ?) ORDER BY dni_cliente";
    
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombreServicio());
            stmt.setString(2, c.getDNI_CIF_cliente());
            result = stmt.executeQuery();

            while (result.next()) {
                Mserv.put(result.getString("descripcion"), result.getInt("cantidad"));
            }
        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
        return Mserv;
	}
	
	//cuenta los turnos que existen para un servicio
	public int countTurnosServicio(Servicio s){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        int i = 0;
        String sql = "SELECT COUNT(*) as C FROM servicios_turnos WHERE (nombre_servicio = ? AND dni_cliente = ?);";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombreServicio());
            stmt.setString(2, s.getCIF_cliente());
            result = stmt.executeQuery();
            
            //i = result.getInt("C");
            if(result.next()){
            	//i++;
            	//i = result.getInt("COUNT(*)");
            	//result.getRow();
            	//i = result.getInt(1);
            	i = result.getInt(result.getRow());
            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return i;
	}
	
	//cuenta los turnos que existen para un servicio
	public int countTurnosServicioTipoTrab(Servicio s, String tipoTrab){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
        ResultSet result = null;
        
        int i = 0;
        String sql = "SELECT COUNT(*) as C FROM servicios_turnos WHERE (nombre_servicio = ? AND dni_cliente = ? AND descripcion = ?);";
        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombreServicio());
            stmt.setString(2, s.getCIF_cliente());
            stmt.setString(3, tipoTrab);
            result = stmt.executeQuery();
            
            //i = result.getInt("C");
            if(result.next()){
            	//i++;
            	//i = result.getInt("COUNT(*)");
            	//result.getRow();
            	//i = result.getInt(1);
            	i = result.getInt(result.getRow());
            }
        } catch (SQLException u) {
            System.out.println("Message: " + u.getMessage());
            System.out.println("SQLState: " + u.getSQLState());
            System.out.println("ErrorCode: " + u.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException u) {
            }
        }
        return i;
	}
	//modifica un servicio
	public void updateServicio(Servicio s){
		Connection conn = ConnectionManager.getInstance().checkOut();
		PreparedStatement stmt = null;
		ResultSet result = null;
		String sql = "UPDATE servicios SET fecha_inicio = ?, fecha_fin = ?, numDias = ? WHERE (nombre_servicio = ? AND CIF_DNI_cliente = ?)";
		
		try{
			stmt = conn.prepareStatement(sql);
			
			stmt.setDate(1, s.getFechaIni());
			stmt.setDate(2, s.getFecha_fin());
            stmt.setInt(3, s.getNumDias());
            
            stmt.setString(4, s.getNombreServicio());
            stmt.setString(5, s.getCIF_cliente());
			
			stmt.executeUpdate();
		}catch(SQLException ex){
			System.out.println("Message: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("ErrorCode: " + ex.getErrorCode());
        } finally {
            try {
            	conn.close();
                if (result != null) {
                    result.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException ex) {
            }
        }
	}
	
	//modificar fila en tabla servicios_tipostrabajadores
	public void updateServicioTipoTrabajador(Map<String,Integer> tt, Servicio s) {
		Iterator<Entry<String, Integer>> it = tt.entrySet().iterator();
        while (it.hasNext()) {
        	Map.Entry<String, Integer> tipoT = (Map.Entry<String, Integer>)it.next();
        
			Connection conn = ConnectionManager.getInstance().checkOut();
			PreparedStatement stmt = null;
			ResultSet result = null;
			
			String sql = "UPDATE servicios_tipostrabajadores SET cantidad = ? WHERE (descripcion = ? AND nombre_servicio = ? AND dni_cliente = ?)";
			
	        try {
	            stmt = conn.prepareStatement(sql);
	            
		    	
		        stmt.setInt(1,tipoT.getValue());
		        
		        stmt.setString(2,tipoT.getKey());
		        stmt.setString(3, s.getNombreServicio());
		        stmt.setString(4, s.getCIF_cliente());
	
		        stmt.executeUpdate();
			}catch(SQLException ex){
				System.out.println("Message: " + ex.getMessage());
	            System.out.println("SQLState: " + ex.getSQLState());
	            System.out.println("ErrorCode: " + ex.getErrorCode());
	        } finally {
	            try {
	            	conn.close();
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException ex) {
	            }
	        }
        }
	}
	
	//borrar un servicio
	public void deleteServicio(Servicio s){
		
		String sql = "DELETE FROM servicios WHERE (nombre_servicio = ? AND CIF_DNI_cliente = ?) ";
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, s.getNombreServicio());
            stmt.setString(2, s.getCIF_cliente());
            stmt.executeUpdate();
        } catch (SQLException e) {
            System.err.println("Message: " + e.getMessage());
            System.err.println("SQLState: " + e.getSQLState());
            System.err.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
            }
        }
	}
}


